﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace DAO
{
    public class KhuyenMaiDAO
    {
        public static DataTable SelectKhuyenMaiAll(ref int tongKhuyenMai)
        {
            SqlConnection conn = new SqlConnection(DataProvider.ConnectionString);
            SqlCommand cmd = new SqlCommand("sp_SelectKhuyenMaiAll", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter para = new SqlParameter("@soKM", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable datatable = new DataTable();
            adapter.Fill(datatable);
            tongKhuyenMai = (int)para.Value;
            conn.Close();
            return datatable;
        }

        public static List<KhuyenMaiDTO> QueryAll()
        {
            SqlConnection conn = new SqlConnection(DataProvider.ConnectionString);
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            SqlCommand cmd = new SqlCommand("Select * from KhuyenMai", conn);
            SqlDataReader reader = cmd.ExecuteReader();

            List<KhuyenMaiDTO> lsKhuyenMaiDTO = new List<KhuyenMaiDTO>();
            if (reader.HasRows == true)
            {
                while (reader.Read())
                {
                    KhuyenMaiDTO khuyenMaiDTO = new KhuyenMaiDTO();
                    khuyenMaiDTO.Ma_KhuyenMai = reader["Ma_KhuyenMai"].ToString();

                    khuyenMaiDTO.Ma_MonAn = reader["Ma_MonAn"].ToString();
                    
                    int temp = 0;
                    //int.TryParse(reader["PhamTramGiam"].ToString(), out temp);
                    //khuyenMaiDTO.PhanTramGiam = int.Parse(reader["PhamTramGiam"].ToString());
                    int.TryParse(reader["PhanTramGiam"].ToString(),out temp);
                    khuyenMaiDTO.PhanTramGiam = temp;
                    DateTime date;
                    //DateTime.TryParse(reader["NgayBatDau"].ToString(), out date);
                    khuyenMaiDTO.NgayBatDau = DateTime.Parse(reader["NgayBatDau"].ToString());

                    DateTime.TryParse(reader["NgayKetThuc"].ToString(), out date);
                    khuyenMaiDTO.NgayKetThuc = date;

                    lsKhuyenMaiDTO.Add(khuyenMaiDTO);
                    
                }
            }
            else
            {
                lsKhuyenMaiDTO = null;
            }
            conn.Close();
            return lsKhuyenMaiDTO;

        }

        public static DataTable SelectKhuyenMaiByAttribute(string maMonAn, int phanTramGiam, DateTime ngayBDTu, DateTime ngayBDDen, DateTime ngayKTTu, DateTime ngayKTDen, ref int tongKhuyenMai)
        {
            SqlConnection conn = new SqlConnection(DataProvider.ConnectionString);
            SqlCommand cmd = new SqlCommand("sp_SelectKhuyenMaiByAttribute", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para;
            para= new SqlParameter("@Ma_MonAn", SqlDbType.NChar, 255);
            para.Direction = ParameterDirection.Input;
            para.Value = maMonAn;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@PhanTramGiam", SqlDbType.Int);
            para.Direction = ParameterDirection.Input;
            para.Value = phanTramGiam;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NgayBDTu", SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = ngayBDTu;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NgayBDDen", SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = ngayBDDen;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NgayKTTu", SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = ngayKTTu;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@NgayKTDen", SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = ngayKTDen;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@soKM", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable datatable = new DataTable();

            adapter.Fill(datatable);
            tongKhuyenMai = (int)para.Value;
            conn.Close();

            return datatable;
        }

        public static DataTable SelectKhuyenMaiByMaMonAn(string maMonAn, ref int tongKhuyenMai )
        {
            SqlConnection conn = new SqlConnection(DataProvider.ConnectionString);
            SqlCommand cmd = new SqlCommand("sp_SelectKhuyenMaiByMaMonAn", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            
            SqlParameter para1 = new SqlParameter("@Ma_MonAn", SqlDbType.NChar, 255);
            para1.Direction = ParameterDirection.Input;
            para1.Value = maMonAn;

            SqlParameter para2 = new SqlParameter("@soKM", SqlDbType.Int);
            para2.Direction = ParameterDirection.Output;
                       
            cmd.Parameters.Add(para1);
            cmd.Parameters.Add(para2);

            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable datatable = new DataTable();

            adapter.Fill(datatable);
            tongKhuyenMai = (int)para2.Value;
            conn.Close();
            return datatable;
        }

        public static KhuyenMaiDTO SelectKhuyenMaiById(string maKhuyenMai)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@Ma_KhuyenMai", maKhuyenMai));
            DataTable dataTable = DataProvider.ExecuteReader("sp_SelectKhuyenMaiById", sqlParamas);

            KhuyenMaiDTO khuyenMaiDTO = new KhuyenMaiDTO();
            if (dataTable.Rows.Count > 0)
            {
                DataRow dataRow = dataTable.Rows[0];
                khuyenMaiDTO.Ma_KhuyenMai = maKhuyenMai;
                khuyenMaiDTO.Ma_MonAn = dataRow["Ma_MonAn"].ToString();
                khuyenMaiDTO.PhanTramGiam = int.Parse(dataRow["PhanTramGiam"].ToString());
                khuyenMaiDTO.NgayBatDau = DateTime.Parse(dataRow["NgayBatDau"].ToString());
                khuyenMaiDTO.NgayKetThuc = DateTime.Parse(dataRow["NgayKetThuc"].ToString());
            }
            else
            {
                khuyenMaiDTO = null;
            }
            return khuyenMaiDTO;
        }

        public static bool InsertKhuyenMai(KhuyenMaiDTO khuyenMaiDTO)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@Ma_KhuyenMai", khuyenMaiDTO.Ma_KhuyenMai));
            sqlParamas.Add(new SqlParameter("@Ma_MonAn", khuyenMaiDTO.Ma_MonAn));
            sqlParamas.Add(new SqlParameter("@PhanTramGiam", khuyenMaiDTO.PhanTramGiam));
            sqlParamas.Add(new SqlParameter("@NgayBatDau", khuyenMaiDTO.NgayBatDau));
            sqlParamas.Add(new SqlParameter("@NgayKetThuc", khuyenMaiDTO.NgayKetThuc));

            return DataProvider.ExecuteNoneQuery("sp_InsertKhuyenMai",sqlParamas);
        }

        public static bool UpdateKhuyenMaiById(KhuyenMaiDTO khuyenMaiDTO)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@Ma_KhuyenMai", khuyenMaiDTO.Ma_KhuyenMai));
            sqlParamas.Add(new SqlParameter("@Ma_MonAn", khuyenMaiDTO.Ma_MonAn));
            sqlParamas.Add(new SqlParameter("@PhanTramGiam", khuyenMaiDTO.PhanTramGiam));
            sqlParamas.Add(new SqlParameter("@NgayBatDau", khuyenMaiDTO.NgayBatDau));
            sqlParamas.Add(new SqlParameter("@NgayKetThuc", khuyenMaiDTO.NgayKetThuc));

            return DataProvider.ExecuteNoneQuery("sp_UpdateKhuyenMaiById", sqlParamas);        
        }

        public static bool DeleteKhuyenMaiById(string maKhuyenMai)
        {
            List<SqlParameter> sqlParamas = new List<SqlParameter>();
            sqlParamas.Add(new SqlParameter("@Ma_KhuyenMai", maKhuyenMai));
            return DataProvider.ExecuteNoneQuery("sp_DeleteKhuyenMaiById", sqlParamas);
        
        }

        public static string CreateKhuyenMaiId()
        {
            DataTable dataTable = DataProvider.ExecuteReader("sp_CreateKhuyenMaiId", null);
            string ID = null;

            if (dataTable.Rows.Count > 0)
            {
                DataRow dataRow = dataTable.Rows[0];
                ID = dataRow[0].ToString();
            }

            return ID;
        }
    }
}
